package gcontent.db;

import gcontent.db.DBParams.Direction;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.Properties;
import java.util.Vector;

public class BaseDB {
	protected Connection connection = null;
	protected String username = "";
	protected String password = null;
	protected String url = "";
	protected String driver = "";
	protected boolean autoCommit = false;
	
	/*private static InputStream getConfigFile() {
		return BaseDB.class.getResourceAsStream("/db.properties");
	}*/
	
	public BaseDB(InputStream is, boolean autoCommit) throws IOException 
	{
		Properties properties = new Properties();
	    try {
	        properties.load(is);
	        this.username = properties.getProperty("username");
			this.password = properties.getProperty("password");
			this.url = properties.getProperty("url");
			this.driver = properties.getProperty("driver");
			this.autoCommit = autoCommit;
	    } catch (IOException io_ex) {
	    	throw io_ex;
	    }		
	}
	
	public BaseDB(HashMap<String, String> config, boolean autoCommit) throws IOException 
	{
	    this.username = config.get("username");
		this.password = config.get("password");
		this.url = config.get("url");
		this.driver = config.get("driver");
		this.autoCommit = autoCommit;	    	
	}

	public BaseDB(){}		

	
	public void connect() throws IllegalAccessException, InstantiationException, ClassNotFoundException, SQLException{
		Class.forName(driver).newInstance ();
		connection = DriverManager.getConnection (url, username, password);
		connection.setAutoCommit(autoCommit);
	}	
	
	public void disconnect() throws SQLException {
		if (connection != null)
			connection.close();
	}
	
	public void rollback() throws SQLException {
		if (connection != null)
			connection.rollback();
	}
	
	public void commit() throws SQLException {
		if (connection != null && !autoCommit)
			connection.commit();
	}	
	
	public ResultSet executeQuery(String query) throws SQLException {
		ResultSet res = null;
		if (connection != null) {
			Statement s = connection.createStatement();
			s.executeQuery(query);
			res = s.getResultSet ();
		}
		return res;
	}

	public boolean isConnected()
	{
		boolean ret_status;
		
		if(connection == null)
			return false;
		
		try {
			ret_status = !connection.isClosed();
		} catch(Exception ex) {
			ex.printStackTrace();
			
			ret_status = false;
		}
		
		return ret_status;
	}
	
	public Vector<DBRow> executeProcedure(String proc_name, DBParams param_values[]) throws SQLException
	{
		CallableStatement cstmt = null;
		String query = "";
		Vector<DBRow> res_q;
			
		res_q = new Vector<DBRow>();
		
		try {
			if (connection != null) {
				ResultSet res;
				ResultSetMetaData rsmd;
				int n_cols;
				
				query = buildQuery(false, proc_name, param_values);
				cstmt = connection.prepareCall(query);
				
				if (param_values != null) {
					for (int i = 0; i < param_values.length; i++) {
						/* get only input parameters */
						if (param_values[i].getDirection() == Direction.d_input) {
							cstmt.setObject(i + 1, param_values[i].getValue());
						} 
					}
				}
				
				cstmt.execute();
				
				res = cstmt.getResultSet();		
		        rsmd = res.getMetaData();
		        n_cols = rsmd.getColumnCount();

				while(res.next()) {
					DBRow row;
					
					row = new DBRow();
					for(int i = 1; i <= n_cols; i++)
						row.set(new DBColumn(rsmd.getColumnName(i), res.getObject(i)));
					
					/* add row */
					res_q.add(row);
				}
				
				res.close();		
			}
		
		} catch (SQLException sql_ex) {
			throw sql_ex;
		} finally {
				if (cstmt != null) {
					if (!cstmt.isClosed()) {
						cstmt.close();
					}
				}
		}
			
		return res_q;
	}
	
	public Object executeProcedure(int funct_ret_type, String proc_name, DBParams param_values[]) throws SQLException {
		CallableStatement cstmt = null;
		String query = "";
		Object ret = null;
			
		try {
			if (connection != null) {
				query = buildQuery((funct_ret_type > -1), proc_name, param_values);
				cstmt = connection.prepareCall(query);

				if (param_values != null) {
					for (int i = 0; i < param_values.length; i++) {
						if (param_values[i].getDirection() == Direction.d_input) {
							cstmt.setObject(i + 1, param_values[i].getValue());
						} else if (param_values[i].getDirection() == Direction.d_output) {
							cstmt.registerOutParameter(i + 1, param_values[i].getType());
						} else if (param_values[i].getDirection() == Direction.d_input_output) {
							cstmt.setObject(i + 1, param_values[i].getValue());
							cstmt.registerOutParameter(i + 1, param_values[i].getType());
						} else if (param_values[i].getDirection() == Direction.d_return_value) {
							cstmt.registerOutParameter(i + 1, funct_ret_type);
						}
					}
				}
				
				cstmt.execute();
				
				if (param_values != null) {
					for (int i = 0; i < param_values.length; i++)
						if (param_values[i].getDirection() == Direction.d_output || 
								param_values[i].getDirection() == Direction.d_input_output)
							param_values[i].setRetValue(cstmt.getObject(i + 1));
				}				
				
				if (funct_ret_type > -1)
					ret = cstmt.getObject(1);
					
				cstmt.close();
			}
		}
		catch (SQLException sql_ex) {
			throw sql_ex;
		} finally {
			if (cstmt != null) {
				if (!cstmt.isClosed()) {
					cstmt.close();
				}
			}
		}	
		
		return ret;
	}
	
	protected String buildQuery(boolean isFunction, String proc_name, DBParams param_values[]) {
		String ret = "";
		String place_holders = "";
		
		if (isFunction)
			ret = "{? = call " + proc_name + "(**)}";
		else
			ret = "{call " + proc_name + "(**)}";

		if (param_values != null) {
			for (int i = 0; i < param_values.length; i++) {
				if (param_values[i].getDirection() != Direction.d_return_value) {
					place_holders += "?";
					if (i < param_values.length - 1)
						place_holders += ", ";
				}
			}
		}
		
		ret = ret.replace("**", place_holders);
		
		return ret;
	}	
}